﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Shutterstock.Salesforce.Tools;
using System.Data.SqlClient;
using Shutterstock.Salesforce.SSISDownload.Logging;
using Shutterstock.Salesforce.Tools.SFPartner;
using System.IO;
using LumenWorks.Framework.IO.Csv;
namespace Shutterstock.Salesforce.SSISDownload.SFTableOperations
{
 /// <summary>
    /// Represents a table refresher
    /// </summary>
    public static class TableRefresher
    {
        /// <summary>
        /// Refresh a table
        /// get new created record and add to sf sql database.
        /// </summary>
        /// <param name="rp"></param>
        public static void Refresh(ReplicationParameter rp, string updateTableName)
        {
            using (SqlConnection Dest = new SqlConnection(rp.DestConnectionString))
            {

                Dest.Open();
                
                // check to see if the table exists in the sql server database
                SqlCommand cmd = Dest.CreateCommand();
                cmd.CommandText = string.Format("select object_id from sys.objects where name = '{0}'", rp.SFTableInfo.TableName);
                object obj = cmd.ExecuteScalar();
                if (obj is DBNull)
                {
                    TableReplicator.Replicate(rp);
                    return;
                }

                // get the timestamp column name
                string columnName = rp.SFTableInfo.GetCreatedDateField();

                // get the most recent record from table and minus 15 min
                cmd.CommandText = string.Format("select max({0}) from [{1}]", columnName, rp.SFTableInfo.TableName);
                obj = cmd.ExecuteScalar();
                DateTime lastCreatedDate = new DateTime(1970, 1, 1);
                if (obj is DBNull || (DateTime)obj < DateTime.Today.AddDays(-29))
                {
                    // if no data in the old table, do table replicate
                    TableReplicator.Replicate(rp);
                    return;
                }
                else
                {
                    lastCreatedDate = ((DateTime)obj).AddMinutes(-15);
                }

                int rows = 0;
                if (updateTableName != String.Empty)
                {
                    bool success = UseLocalData(rp, Dest, updateTableName) ;
                    dropUpdateTable(Dest,updateTableName);
                    if (success)
                    {
                        return;
                    }
                }



                string lastCreateSqlServer = lastCreatedDate.ToString("yyyy-MM-dd HH:mm:ss");
                string lastCreateSalesforce = lastCreatedDate.ToString("yyyy-MM-ddTHH:mm:sszzz");
                // create a dictionary with all data ID 
                cmd.CommandText = string.Format("select Id from [{0}] where {1} >= '{2}'", rp.SFTableInfo.TableName, columnName, lastCreateSqlServer);
                SqlDataReader dataReader = cmd.ExecuteReader();
                Dictionary<string, bool> Ids = new Dictionary<string, bool>();
                while (dataReader.Read())
                {
                    Ids.Add(dataReader["Id"].ToString(), true);
                }
                dataReader.Close();

                // get all new record from sfsource, remove the duplicates from sf new data first before insert to the table
                string query = string.Format(String.Format("{0}where {{0}} >= {{1}}", rp.SFTableInfo.GenerateSelectAll()), columnName, lastCreateSalesforce );

                string CountQuery = String.Format("Select COUNT() FROM {0} where {1} >= {2}", rp.SFTableInfo.TableName, columnName, lastCreateSalesforce
                    );
                

                
                
                int affectedRecords = 0;
                
                DateTime start = DateTime.Now;
                
                
                if (!rp.UseBulkAPI)
                {
                    rows = UseStandardAPI(rp, Dest, cmd, query, start,Ids);
                }
                else
                {
                    QueryResult qr = rp.SalesForceConnection.SalesForceService.query(CountQuery);
                    if (qr.size > 0)
                    {
                        affectedRecords = qr.size;
                    }
                                            
                    if (affectedRecords >= rp.BulkRowCutoff)
                    {
                            rows = UseBulkAPI(rp, Dest, cmd, query, start,Ids);
                    }
                     else
                    {
                          rows = UseStandardAPI(rp, Dest, cmd, query, start, Ids);
                    }
                }
                

                
                
                

                

            }

        }

        private static void dropUpdateTable(SqlConnection dest, string updateTableName)
        {
            if (dest.State != ConnectionState.Open)
            {
                dest.Open();
            }

            StringBuilder drop = new StringBuilder();
            drop.AppendLine(String.Format(" IF object_id('{0}') is not null", updateTableName));
            drop.AppendLine(" begin");
            drop.AppendLine(String.Format(" Drop Table [{0}] ", updateTableName));
            drop.AppendLine(" end");

            using (SqlCommand cmd = dest.CreateCommand())
            {
                cmd.CommandTimeout = 0;
                cmd.CommandText = drop.ToString();
                cmd.ExecuteNonQuery();
            }
            return;

        }
        private static bool UseLocalData(ReplicationParameter rp, SqlConnection dest,string stagingTableName)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder fieldList = new StringBuilder();
            sb.AppendFormat("Insert into [{0}]", rp.SFTableInfo.TableName);
            sb.AppendLine("(");
            bool needsComma = false;
            foreach (string column in rp.SFTableInfo.ColumnNames)
            {
                if (needsComma)
                {
                    fieldList.Append(",");
                }
                fieldList.Append(column);
                needsComma = true;
            }
            

            sb.AppendLine(fieldList.ToString());
            sb.AppendLine(")");
            sb.AppendFormat("Select {0} from {1}", fieldList.ToString(), stagingTableName);
            sb.AppendFormat(" where Not Exists ( select 1 from [{1}] where {0}.id = [{1}].id ) ", stagingTableName, rp.SFTableInfo.TableName);

            if (dest.State != ConnectionState.Open)
            {
                dest.Open();
            }

            using (SqlCommand cmd = dest.CreateCommand())
            {
                cmd.CommandTimeout = 0;
                cmd.CommandText = sb.ToString();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    return false;
                }

                return true;
            }

            

        }
        private static int UseBulkAPI(ReplicationParameter rp, SqlConnection Dest, SqlCommand cmd, string query, DateTime start, Dictionary<string, bool> Ids)
        {
            var bqs = rp.SalesForceConnection;
            string path = String.Format("{0}/{1}Refresh.csv", rp.BulkPath, rp.SFTableInfo.TableName);
            if (File.Exists(path))
                File.Delete(path);
            bqs.ExecuteBlockingQuery(rp.SFTableInfo.TableName, ConcurrencyMode.Parallel, query, 30000, path);

            using (TextReader tr = new StreamReader(path))
            {
                Logger.Log(FunctionType.BulkQuery, rp.SFTableInfo.TableName, TableTaskType.Update, start, -1);
                string firstLine = tr.ReadLine();
                if (firstLine != "Records not found for this query") ;
                {

                    ((StreamReader)tr).BaseStream.Position = 0;
                    ((StreamReader)tr).DiscardBufferedData();


                    using (CsvReader csv = new CsvReader(tr, true))
                    {
                        using (CSVDataReaderAdapter adapter = new CSVDataReaderAdapter(csv))
                        {
                            List<string> decimals = TableUtilities.GetDBDecimals(rp.SFTableInfo.TableName, Dest);
                            foreach (string decimalColumn in decimals)
                            {
                                adapter.AddDecimalOveride(decimalColumn);
                            }


                            IDataReader reader = adapter;
                            DataTable newData = new DataTable();

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                newData.Columns.Add(reader.GetName(i).ToLower(), reader.GetFieldType(i));
                            }

                            while (reader.Read())
                            {
                                if (!Ids.ContainsKey(reader["id"].ToString()))
                                {
                                    object[] rowArray = new object[reader.FieldCount];
                                    reader.GetValues(rowArray);
                                    DataRow row = newData.NewRow();
                                    row.ItemArray = rowArray;
                                    newData.Rows.Add(row);

                                }
                            }



                            SqlConnection conn = Dest;
                            if (conn.State != ConnectionState.Open)
                            {
                                conn.Open();
                            }
                            string tablename = rp.SFTableInfo.TableName;

                            Dictionary<string, string> lookup = TableUtilities.GetDBCasedNames(tablename, conn);

                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                            {

                                for (int i = 0; i < newData.Columns.Count; i++)
                                {
                                    string s1 = newData.Columns[i].ColumnName;
                                    bulkCopy.ColumnMappings.Add(s1, lookup[s1.ToUpper()]);
                                }

                                bulkCopy.DestinationTableName = string.Format("[{0}]", rp.SFTableInfo.TableName);
                                bulkCopy.BatchSize = rp.BatchSize;
                                bulkCopy.BulkCopyTimeout = 0; ;
                                bulkCopy.WriteToServer(newData);
                            }
                        }
                    }
                }
            }
            try
            {
                File.Delete(path);
            }
            catch
            {
            }
            return - 1;
        }
        private static int UseStandardAPI(ReplicationParameter rp, SqlConnection Dest, SqlCommand cmd, string query, DateTime start, Dictionary<string, bool> Ids)
        {
            int rows;

            DataTable dt = rp.SalesForceConnection.SalesForceService.GetDataTable(query, rp.SFTableInfo);
            Logger.Log(FunctionType.WebServiceGetTable, rp.SFTableInfo.TableName, TableTaskType.Refresh, start, dt.Rows.Count);


            start = DateTime.Now;

            DataTable newData = dt.Clone();
            foreach (DataRow dr in dt.Rows)
            {
                if (!Ids.ContainsKey(dr["Id"].ToString()))
                {
                    newData.Rows.Add(dr.ItemArray);
                }
            }
            start = DateTime.Now;
            // using Builk Insert.
            using (SqlBulkCopy BulkDestination = new SqlBulkCopy(Dest))
            {
                BulkDestination.DestinationTableName = string.Format("[{0}]", rp.SFTableInfo.TableName);
                BulkDestination.BulkCopyTimeout = 0;
                BulkDestination.BatchSize = rp.BatchSize;
                BulkDestination.WriteToServer(newData);
            }
            Logger.Log(FunctionType.BulkLoad, rp.SFTableInfo.TableName, TableTaskType.Refresh, start, newData.Rows.Count);


            rows = dt.Rows.Count;
            return rows;
        }
    }
}

